﻿//-----------------------------------------------------------------------
// <copyright file="CustomViewBuilder.cs" company="n.a.">
//     Copyright Dave999. All rights reserved.
// </copyright>
//-----------------------------------------------------------------------
[module: System.Diagnostics.CodeAnalysis.SuppressMessage("StyleCop.CSharp.DocumentationRules", "SA1600:ElementsMustBeDocumented", Justification = "To be returned to .")]

namespace DbView
{
	using System;
	using System.Data;
	/*
		Class to manage the ins and outs of converting whatever SQL is in the
		adhoc qurey window into the basis for a custom view.
	*/
	class CustomViewBuilder
	{
		internal bool CheckSql(string sql, out string message)
		{
			// remove crlf to make regex searching work
			string searchSql = sql.Replace("\n", " ").Replace("\r", " ");

			// 1. Must begin with Select
			SimpleRegex reg1 = new SimpleRegex(@"^\s*select\s+");
			if (!reg1.MatchTarget(searchSql))
			{
				return this.ValidationFailed("Incorrect Sql format to use for creating a custom view", out message);
			}

			// TODO validate 
			message = "";
			return true;
		}

		private bool ValidationFailed(string err, out string message)
		{
			message = err + Help;
			return false;
		}

		internal ViewDefinition MakeDefinition(string sql, DbViewDriver.IDataSource iDataSource, string currentConnection, out string message)
		{
			// assume here with a sql select from
			String template = @"( {0} ) {1}";

			String viewDefName = this.MakeNewViewName(currentConnection);
			String tableName = string.Format(template, sql, viewDefName);

			// Get the dataset: Use the adhoc command interface API as in the MainPresenter class
			Command adhocCommand = new Command(Commands.Cmnd.Adhoc);
			ViewContext vc = new ViewContext(adhocCommand);
			vc.Command = adhocCommand;
			vc.FilterString = "select * from " + tableName;    // Clunky, but works

			try
			{
				// check connection info attached to the source.
				DataTable dt = iDataSource.BuildTable(adhocCommand, vc);
				DataTableExtraInfoManager mgr = new DbView.DataTableExtraInfoManager(dt);
				message = mgr.GetExtraInfo("MESSAGE", "");

				// make the def
				dt.TableName = tableName;
				ViewDefinition Definition = ViewDefinitionManager.makeNewViewDefinition(dt);
				Definition.Name = viewDefName;
				Definition.SetPermanent(true); // force this to permanent to make it appear in the list.
				return Definition;
			}
			catch (Exception exc)
			{
				message = exc.Message + Help;
				return null;
			}
		}

		private string MakeNewViewName(String currentConnection)
		{
			String defaultNameRoot = "Custom_View_";

			int viewIndex = 0;
			while (true)
			{
				++viewIndex;
				String viewName = defaultNameRoot + viewIndex.ToString();
				// break the loop when a view name is not found.
				if (ViewDefinitionManager.FindView(viewName, currentConnection) == null)
				{
					return viewName;
				}

				if (viewIndex > 10000)
				{
					throw new Exception("Wierd looping error in MakeNewViewName");
				}
			}
		}

		internal static string GetInstructionsOnFailedPreconditions()
		{
			return BasicHelp;
		}

		const string BasicHelp =
@"
A custom DbViewSharp view is created by entering a select statement in
the ad-hoc query window and selecting the menu item that you have
just done. The advantage of this is that you can join tables or
select from objects other than tables. Once created the view may
be renamed and modified through the view edit options.

At present the ad-hoc window is not showing or you have no SQL entered. 
";


		const string Help =
@"
The sql used to form a custom view must be a valid SELECT statemnt with all fields named
and no duplicates. Select top n is not allowed. Neither is an order by clause. Joins and Where
clauses are permitted.
Examples:
Permissable : 
select e.EmployeeID, e.NationalIDNumber, e.ManagerID, e.Title as EmpTitle,
       c.ContactID, c.NameStyle, c.Title, c.FirstName, c.MiddleName, c.LastName
  from HumanResources.Employee e
  join Person.Contact c on c.ContactID = e.ContactID
Not Permissable : 
select top 10 *
  from Person.Contact
 order by Title
";
	}
}
